package umg.util;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Collections;
import java.util.Map;

import umg.Constants;
import umg.Messages;
import umg.gui.GUI;
import umg.vo.MatchSnapShotVO;
import umg.vo.PlayerVO;

public class DBUtil
{

	private static Connection conn;// shared in-memory db conn
	static boolean inMemory = false;
	static boolean schematested = false;
	private static final Object syncObject = new Object();
	private static final int QUERY_CACHE_SIZE = 200, PLAYER_CACHE_SIZE=200;
	private static CountryLookup countryLookup = new CountryLookup();
	static Map<String, String> queryCache = Collections.synchronizedMap(new LruCache<String, String>(QUERY_CACHE_SIZE));
	private static Map<String, Long> playerCache= Collections.synchronizedMap(new LruCache<String, Long>(PLAYER_CACHE_SIZE));;
	private static Map<Long, String> playerAliasCache= Collections.synchronizedMap(new LruCache<Long, String>(PLAYER_CACHE_SIZE));

	public DBUtil()
	{
		try
		{
			// connect to the in-memory db
			if (conn == null || conn.isClosed())
			{
				connect();
			}
		}
		catch (SQLException e)
		{
			e.printStackTrace();
			System.out.println(Messages.getString("HSQLDBUtil.0") + e.getMessage());
		}
	}

	static Class<?> driverClass = null;
	static String dbfolder = null;
	static boolean folderChecked = false;

	public void connect()
	{ // note more general exception
		// Load the HSQL Database Engine JDBC driver
		// hsqldb.jar should be in the class path or made part of the current
		// jar

		try
		{
			if (conn != null && conn.isClosed() == false)
			{
				return;
			}
			if (driverClass == null)
			{
				driverClass = Class.forName(PropertyManager.getDriverClassname());
			}

			// connect to the database. This will load the db files and start
			// the
			// database if it is not already running.
			// db_file_name_prefix is used to open or create files that hold the
			// state
			// of the db.
			// It can contain directory names relative to the
			// current working directory
			if (PropertyManager.isDebug())
			{
				System.out.println(Messages.getString("HSQLDBUtil.1") + PropertyManager.getDburl());
			}
//			// check if the folder for the database exists
//			if (folderChecked == false)
//			{
//				dbfolder = PropertyManager.getDburl().substring(PropertyManager.getDburl().lastIndexOf(':') + 1, PropertyManager.getDburl().lastIndexOf('/'));
//				File dbFolderObj = new File(dbfolder);
//				if (!dbFolderObj.exists())
//				{
//					dbFolderObj.mkdir();
//					System.out.println(Messages.getString("HSQLDBUtil.2") + dbfolder);
//				}
//				folderChecked = true;
//			}

			synchronized (syncObject)
			{
				if (conn == null)
				{
					//TODO:leaks int[] for some reason (10-16-2009)
					GUI.logToStatus("Trying to connect to: "+PropertyManager.getDburl());
					conn = DriverManager.getConnection(PropertyManager.getDburl(), PropertyManager.getDbUsername(), PropertyManager.getDbPassword());
				}
				GUI.logToStatus(Messages.getString("HSQLDBUtil.5"));
//				if (schematested == false)
//				{
//					createSchemaTest();
//					System.out.println(Messages.getString("HSQLDBUtil.6"));
//					schematested = true;
//				}

			}
		}
		catch (Exception e)
		{
			if (e instanceof ClassNotFoundException)
			{
				GUI.logToStatus(Messages.getString("HSQLDBUtil.9"));
				System.exit(-1);
				e.printStackTrace();
			}
			else if (e.getMessage().indexOf(Messages.getString("HSQLDBUtil.10")) > -1 || e.getMessage().indexOf(Messages.getString("HSQLDBUtil.11")) > -1)
			{
				System.out.println(Messages.getString("HSQLDBUtil.0") + e.getMessage());
				e.printStackTrace();
				System.exit(-1);
			}
			else
			{
				e.printStackTrace();
				System.out.println(Messages.getString("HSQLDBUtil.0") + e.getMessage());
			}
		}

	}

	// use for SQL command SELECT
	public void queryPrint(String expression)
	{
		Statement st = null;
		ResultSet rs = null;

		try
		{
			if (conn == null || conn.isClosed() /* || testDBConn(conn) == false */)
			{
				connect();
			}
		}
		catch (SQLException e1)
		{
			e1.printStackTrace();
		}

		try
		{
			st = conn.createStatement();
			// repeated calls to execute but we
			// choose to make a new one each time
			rs = st.executeQuery(expression); // run the query
			// do something with the result set.
			print(rs);
			st.close(); // NOTE!! if you close a statement the associated
			// ResultSet is
			// closed too
			// so you should copy the contents to some other object.
			// the result set is invalidated also if you recycle an Statement
			// and try to execute some other query before the result set has
			// been
			// completely examined.
		}
		catch (SQLException e)
		{
			e.printStackTrace();
			System.out.println(Messages.getString("HSQLDBUtil.34") + e.getMessage());
		} // statement objects can be reused with
	}

	
	// use for SQL command SELECT returns a string
	public String queryOneValue(String expression)
	{
		if(queryCache.get(expression)!=null)
		{
			return queryCache.get(expression);
		}
		Statement st = null;
		ResultSet rs = null;
		long start=System.currentTimeMillis();
		if (PropertyManager.isDebug())
		{
			System.out.println(Messages.getString("HSQLDBUtil.36") + expression);
		}

		try
		{
			if (conn == null || conn.isClosed()/* || testDBConn(conn) == false */)
			{
				connect();
			}
		}
		catch (SQLException e1)
		{
			e1.printStackTrace();
		}

		try
		{
			st = conn.createStatement();
			// repeated calls to execute but we
			// choose to make a new one each time
			rs = st.executeQuery(expression); // run the query
			
			// do something with the result set.
			Object o = null;

			// the result set is a cursor into the data. You can only
			// point to one row at a time
			// assume we are pointing to BEFORE the first row
			// rs.next() points to next row and returns true
			// or false if there is no next row, which breaks the loop
			if (rs.next())
			{
				o = rs.getObject(1); // In SQL the first column is indexed with
				System.out.println("Query took "+(System.currentTimeMillis()-start) +"ms");
				// 1 not 0// first value is returned
				queryCache.put(expression,o.toString());
				return (o.toString());
			}
			st.close(); // NOTE!! if you close a statement the associated
			// ResultSet is
			// closed too
			// so you should copy the contents to some other object.
			// the result set is invalidated also if you recycle an Statement
			// and try to execute some other query before the result set has
			// been
			// completely examined.
		}
		catch (SQLException e)
		{
			// e.printStackTrace();
			System.out.println(Messages.getString("HSQLDBUtil.37") + e.getMessage());
		} // statement objects can be reused with
		return Messages.getString("HSQLDBUtil.39");// not found or error
	}

	public static void print(ResultSet rs) throws SQLException
	{

		// the order of the rows in a cursor
		// are implementation dependent unless you use the SQL ORDER statement
		ResultSetMetaData meta = rs.getMetaData();
		int colmax = meta.getColumnCount();
		int i;
		Object o = null;

		// the result set is a cursor into the data. You can only
		// point to one row at a time
		// assume we are pointing to BEFORE the first row
		// rs.next() points to next row and returns true
		// or false if there is no next row, which breaks the loop
		for (; rs.next();)
		{
			for (i = 0; i < colmax; ++i)
			{
				o = rs.getObject(i + 1); // In SQL the first column is
				// indexed

				// with 1 not 0
				System.out.print(o.toString() + Messages.getString("HSQLDBUtil.37"));
			}

			System.out.println(Messages.getString("HSQLDBUtil.37"));
		}
	} // void dump( ResultSet rs )

	public int update(String expression)
	{
		return update(expression, false);
	}

	// use for SQL commands CREATE, DROP, INSERT and UPDATE
	// used the global memory-db conenction
	public int update(String expression, boolean ignoreExceptions)
	{

		Statement st = null;
		if (PropertyManager.isDebug())
		{
			System.out.println(Messages.getString("HSQLDBUtil.36") + expression);
		}
		try
		{
			if (conn == null || conn.isClosed() /* || testDBConn(conn) == false */)
			{
				connect();
			}
		}
		catch (SQLException e1)
		{
			if (!ignoreExceptions) e1.printStackTrace();
		}
		try
		{
			st = conn.createStatement();
			int i = st.executeUpdate(expression); // run the query
			st.close();
			return i;
		}
		catch (SQLException e)
		{
			// dont print errors for table exists, table not found
			if (!ignoreExceptions) if (e.getMessage().indexOf(Messages.getString("HSQLDBUtil.43")) == -1 // exists
					&& e.getMessage().indexOf(Messages.getString("HSQLDBUtil.44")) == -1 && e.getMessage().indexOf("Table not found") == -1)// Table
																																			// not
																																			// found
			{
				e.printStackTrace();
				System.out.println("SQL Exception " + e.getMessage());
				System.out.println(Messages.getString("HSQLDBUtil.45") + expression + Constants.NEWLINE);
			}
		} // statements
		return -1;
	} // update()


	/*
	 * returns the match_id
	 */
	public int saveMatchSnapShot(MatchSnapShotVO match, int url_number)
	{
		if (match == null)
		{
			return 0;
		}
		// save the players to players
		PlayerVO players[] = match.getPlayers();
		String name, ip, aliases, guid;
		// String ping = "", score = "", team = "",
		int playerid = 0;

		int pingTotal = 0;// count ping
		for (int row = 0; row < players.length; row++)
		{
			// count pings and dont save if there are all bots. (sum=0)
			pingTotal += Integer.parseInt(players[row].getPing());
		}

		String serverName = PropertyManager.getWebadminURLNames()[url_number];
		for (int row = 0; row < players.length && pingTotal > 0; row++)
		{
			// System.out.println("row= "+row);
			// data[row][0]=players[row].getTeam()+">>>>";//the cell renderer
			// will pick this up and change its color
			name = players[row].getPlayerName();
			// ping = players[row].getPing();
			// score = players[row].getScore();
			ip = players[row].getIP();
			// team = players[row].getTeam();
			guid = players[row].getGlobalID();

			// check if this ip is already in the database table
			Long playerId = (Long) playerCache.get(ip);
			if (playerId != null)// found in cache
			{
				playerid = playerId.intValue();
			}
			else
			{
				//check the database
				playerid = getPlayerIDForIP(ip);
				if (playerid != -1)//found, but wasnt in the cache.. Add to the Cache
				{
					playerCache.put(ip, new Long(playerid));
					if (PropertyManager.isDebug())
					{
						System.out.println(Messages.getString("HSQLDBUtil.70") + playerCache.size());
					}
				}
			}
			if (playerid == -1)
			{// ip not found, ok to insert to players
				if (name.indexOf(Messages.getString("HSQLDBUtil.71")) != -1)
				{
					name = StringUtils.oldReplaceAll(name, Messages.getString("HSQLDBUtil.71"), Messages.getString("HSQLDBUtil.73"));
					if (PropertyManager.isDebug())
					{
						System.out.println(Messages.getString("HSQLDBUtil.74") + name);
					}
				}
				if (name.length() > 0)
				{ //name, ip, aliases, update_ts
					update(Messages.getString("HSQLDBUtil.75") + Messages.getString("HSQLDBUtil.76") + Messages.getString("HSQLDBUtil.77") + name + Messages.getString("HSQLDBUtil.78") + ip + Messages.getString("HSQLDBUtil.79") + name + Messages.getString("HSQLDBUtil.80") + guid + Messages.getString("HSQLDBUtil.80") + StringUtils.getTimestampFromDate(match.getTimestamp()) + Messages.getString("HSQLDBUtil.81"));
					try
					{
						GUI.logToStatus(serverName + Messages.getString("GUI.160") + Messages.getString("HSQLDBUtil.82") + name + Messages.getString("HSQLDBUtil.83") + Constants.SPACE_STR + ip);
					}
					catch (Exception e)
					{
						e.printStackTrace();
					}
				}
				// set playerid
				playerid = getPlayerIDForIP(ip);
				if (playerid != -1)
				{
					playerCache.put(ip, new Long(playerid));
					if (PropertyManager.isDebug())
					{
						System.out.println(Messages.getString("HSQLDBUtil.84") + playerCache.size());
					}
				}
			}// unknown ip
			else
			{// ip was found, but the player may be aliasing. add to the
				// players known alias
				playerId = new Long(playerid);
				aliases = playerAliasCache.get(playerId);
				if (aliases == null)
				{
					aliases = getPlayerAliasesForID(playerid);
					playerAliasCache.put(playerId, aliases);
					if (PropertyManager.isDebug())
					{
						System.out.println(Messages.getString("HSQLDBUtil.85") + playerAliasCache.size());
					}
				}
				if (aliases != null && aliases.indexOf(name) == -1)
				{
					// alias was not found, add it
					aliases += Constants.ALIAS_SEPERATOR + name;
					// update the alias list.
					updateAliasForPlayerID(playerid, aliases, match.getTimestamp());
					playerAliasCache.put(playerId, aliases);// update cache
					try
					{
						GUI.logToStatus(serverName + Messages.getString("GUI.160") + Messages.getString("HSQLDBUtil.86") + ip + Messages.getString("HSQLDBUtil.87") + StringUtils.oldReplaceAll(aliases, Constants.ALIAS_SEPERATOR, Messages.getString("HSQLDBUtil.88")) + Messages.getString("HSQLDBUtil.89")); //$NON-NLS-3$ //$NON-NLS-4$
					}
					catch (Exception e)
					{
						e.printStackTrace();
					}
				}// alias check
				else
				{// alias was found do nothing
					if (PropertyManager.isDebug())
					{
						System.out.println(Messages.getString("HSQLDBUtil.90") + name + Messages.getString("HSQLDBUtil.91") + ip);
					}
				}// alias check

				//also check if we have a new guid, if so save it!
				if (guid != null && guid.length() > 0)
				{
					String guids = getPlayerGUIDsForID(playerId.intValue());
					//no guid previously
					if (guids == null || guids.length() == 0)
					{
						updateGUIDsForPlayerID(playerid, guid, match.getTimestamp());
						GUI.logToStatus(serverName + Messages.getString("GUI.160") + Messages.getString("HSQLDBUtil.NEWGUID") + ip + Constants.COMMA_STR + guid);
					}
					//new guid
					else if (guids != null && guids.indexOf(guid) == -1)
					{
						// alias was not found, add it
						guids += Constants.COMMA_STR + Constants.SPACE_STR + guid;
						updateGUIDsForPlayerID(playerid, guids, match.getTimestamp());
						GUI.logToStatus(serverName + Messages.getString("GUI.160") + Messages.getString("HSQLDBUtil.NEWGUID") + ip + Constants.COMMA_STR + guids);

					}
				}
				//end guid check
			}// existing ip

		}// for

		// select to check;
		// queryPrint("select * from MATCH_HISTORY");
		// queryPrint("select * from PLAYERS");
		return 0;
	}

	private void updateAliasForPlayerID(int playerid, String aliases, java.util.Date timestamp)
	{
		if (aliases.indexOf('\'') >= 0)
		{
			aliases = StringUtils.oldReplaceAll(aliases, Messages.getString("HSQLDBUtil.71"), Messages.getString("HSQLDBUtil.73"));
			if (PropertyManager.isDebug())
			{
				System.out.println(Messages.getString("HSQLDBUtil.94") + aliases);
			}
		}
		update(Messages.getString("HSQLDBUtil.95") + aliases + Messages.getString("HSQLDBUtil.96") + StringUtils.getTimestampFromDate(timestamp) + Messages.getString("HSQLDBUtil.97") + playerid); //$NON-NLS-3$
	}

	private void updateGUIDsForPlayerID(int playerid, String guids, java.util.Date timestamp)
	{
		if (guids.indexOf('\'') >= 0)
		{
			guids = StringUtils.oldReplaceAll(guids, Messages.getString("HSQLDBUtil.71"), Messages.getString("HSQLDBUtil.73"));
			if (PropertyManager.isDebug())
			{
				System.out.println(Messages.getString("HSQLDBUtil.94") + guids);
			}
		}
		update(Messages.getString("HSQLDBUtil.UpdateGUIDS") + guids + Messages.getString("HSQLDBUtil.96") + StringUtils.getTimestampFromDate(timestamp) + Messages.getString("HSQLDBUtil.97") + playerid); //$NON-NLS-3$
	}

	private int getPlayerIDForIP(String ip)
	{
		String id = queryOneValue(Messages.getString("HSQLDBUtil.98") + ip + Messages.getString("HSQLDBUtil.71"));
		return Integer.parseInt(id);
	}

	private String getPlayerAliasesForID(int id)
	{
		String aliases = queryOneValue(Messages.getString("HSQLDBUtil.100") + id + Messages.getString("HSQLDBUtil.71"));
		return aliases;
	}

	private String getPlayerGUIDsForID(int id)
	{
		String guids = queryOneValue(Messages.getString("HSQLDBUtil.GetGUIDSForPlayer") + id + Messages.getString("HSQLDBUtil.71"));
		return guids;
	}

	public String searchByName(String name)
	{
		// System.out.println("Running Search By name");
		StringBuilder results = new StringBuilder();
		results.append(Messages.getString("HSQLDBUtil.102"));
		results.append(name);
		results.append(Messages.getString("HSQLDBUtil.103"));

		Statement st = null;
		ResultSet rs = null;

		try
		{
			if (conn == null || conn.isClosed() /* || testDBConn(conn) == false */)
			{
				connect();
			}
		}
		catch (SQLException e1)
		{
			e1.printStackTrace();
		}

		try
		{
			st = conn.createStatement();
			// run the query
			// newer players show up first
			rs = st.executeQuery(Messages.getString("HSQLDBUtil.104") + name.toLowerCase() + Messages.getString("HSQLDBUtil.71") + Messages.getString("HSQLDBUtil.122a"));// was
			// HSQLDBUtil.105='
			// order
			// by
			// player_id
			// desc

			int playerid = 0;
			String aliases = Messages.getString("HSQLDBUtil.4");
			String ip = Messages.getString("HSQLDBUtil.4");
			String guid = Messages.getString("HSQLDBUtil.4");
			for (; rs.next();)
			{
				// process each row
				playerid = rs.getInt(1);
				aliases = rs.getString(2);
				aliases = StringUtils.oldReplaceAll(aliases, Constants.ALIAS_SEPERATOR, Messages.getString("HSQLDBUtil.108"));
				ip = rs.getString(3);
				guid = rs.getString(4);
				results.append(Messages.getString("HSQLDBUtil.109"));
				results.append(ip);
				results.append(Messages.getString("HSQLDBUtil.110"));
				results.append(Messages.getString("HSQLDBUtil.111"));
				results.append(aliases);
				results.append(Messages.getString("HSQLDBUtil.112"));

				results.append(Messages.getString("HSQLDBUtil.GUID"));
				if (guid != null) results.append(guid);
				results.append(Messages.getString("HSQLDBUtil.128"));

				results.append(Messages.getString("HSQLDBUtil.99"));
				results.append(countryLookup.locate(ip).getValue2());
				results.append(Messages.getString("HSQLDBUtil.128"));

				results.append(Messages.getString("HSQLDBUtil.113"));
				results.append(getPlayerLastSeen(playerid));
				results.append(Messages.getString("HSQLDBUtil.114"));
			}
			st.close();
		}
		catch (SQLException e)
		{
			e.printStackTrace();
			System.out.println(Messages.getString("HSQLDBUtil.115") + e.getMessage());
		}
		results.append(Messages.getString("HSQLDBUtil.152"));
		return results.toString();
	}

	public String getPlayerLastSeen(int playerid)
	{

		String val = queryOneValue(Messages.getString("HSQLDBUtil.116") + playerid);
		if (val.equals(Messages.getString("HSQLDBUtil.117")))
		{
			return Messages.getString("HSQLDBUtil.4");
		}
		else
		{
			return val.substring(0, val.lastIndexOf(Messages.getString("HSQLDBUtil.119")));
		}
	}

	public String searchByIP(String ipin)
	{
		StringBuilder results = new StringBuilder();
		results.append(Messages.getString("HSQLDBUtil.120"));
		results.append(ipin);
		results.append(Messages.getString("HSQLDBUtil.121"));

		Statement st = null;
		ResultSet rs = null;

		try
		{
			if (conn == null || conn.isClosed() /* || testDBConn(conn) == false */)
			{
				connect();
			}
		}
		catch (SQLException e1)
		{
			e1.printStackTrace();
		}

		try
		{
			st = conn.createStatement();
			// run the query
			rs = st.executeQuery(Messages.getString("HSQLDBUtil.122") + ipin + Messages.getString("HSQLDBUtil.71") + Messages.getString("HSQLDBUtil.122a"));

			int playerid = 0;
			String aliases = Messages.getString("HSQLDBUtil.4");
			String ip = Messages.getString("HSQLDBUtil.4");
			String guid = Messages.getString("HSQLDBUtil.4");
			for (; rs.next();)
			{
				// process each row
				playerid = rs.getInt(1);
				aliases = rs.getString(2);
				aliases = StringUtils.oldReplaceAll(aliases, Constants.ALIAS_SEPERATOR, Messages.getString("HSQLDBUtil.126"));
				ip = rs.getString(3);
				guid = rs.getString(4);
				results.append(Messages.getString("HSQLDBUtil.127"));
				results.append(ip);
				results.append(Messages.getString("HSQLDBUtil.128"));
				results.append(Messages.getString("HSQLDBUtil.129"));
				results.append(aliases);
				results.append(Messages.getString("HSQLDBUtil.130"));

				results.append(Messages.getString("HSQLDBUtil.GUID"));
				if (guid != null) results.append(guid);
				results.append(Messages.getString("HSQLDBUtil.128"));

				results.append(Messages.getString("HSQLDBUtil.99"));
				results.append(countryLookup.locate(ip).getValue2());
				results.append(Messages.getString("HSQLDBUtil.128"));

				results.append(Messages.getString("HSQLDBUtil.131"));
				results.append(getPlayerLastSeen(playerid));
				results.append(Messages.getString("HSQLDBUtil.132"));

			}
			st.close();
		}
		catch (SQLException e)
		{
			e.printStackTrace();
			System.out.println(Messages.getString("HSQLDBUtil.133") + e.getMessage());
		}
		results.append(Messages.getString("HSQLDBUtil.152"));
		return results.toString();
	}

	public String getDBStats()
	{
		StringBuilder stats = new StringBuilder();
		int pCount;
		// int shotCount;
		// long dbFileSize;
		pCount = Integer.parseInt(queryOneValue(Messages.getString("HSQLDBUtil.134")));
		// shotCount =
		//Integer.parseInt(queryOneValue("select count(*) from match_history"));
		// this.shutdown(true);
		// String dbfilename =
		//PropertyManager.getHsql_dburl().substring(PropertyManager.getHsql_dburl
		// ().lastIndexOf(":")
		// + 1) + ".data";
		// File dbFile = new File(dbfilename);
		// dbFileSize = dbFile.length();
		stats.append(Messages.getString("HSQLDBUtil.135"));
		stats.append(pCount);
		stats.append(Messages.getString("HSQLDBUtil.136"));
		// stats.append("Number of Match Snapshots: ");
		// stats.append(shotCount);
		// stats.append("\n");
		// stats.append("Database size on disk: ");
		// stats.append(dbFileSize / 1024);
		// stats.append(" KB");

		//run only when needed: -> system console
		//CountryLookup cl= new CountryLookup();
		//cl.getCountries(conn);
		return stats.toString();
	}

	public boolean doesIPHaveAliases(String ip)
	{
		Statement st;
		try
		{
			if (conn == null || conn.isClosed() /* || testDBConn(conn) == false */)
			{
				conn = null;
			}
			st = conn.createStatement();
			ResultSet rs = st.executeQuery(Messages.getString("HSQLDBUtil.137") + ip + Messages.getString("HSQLDBUtil.71"));
			if (rs.next())
			{
				if (rs.getString(1).indexOf(Constants.ALIAS_SEPERATOR) > -1)
				{
					return true;
				}
			}
		}
		catch (SQLException e)
		{
			e.printStackTrace();
		}
		// run the query

		return false;
	}

	// update all player rows whose alias starts with teh seperator.
	// set the first alias seperator with name+seperator

	public void fixAliasIssue()
	{
		// System.out.println("Running Search By name");
		Statement st = null;
		ResultSet rs = null;

		try
		{
			if (conn == null || conn.isClosed() /* || testDBConn(conn) == false */)
			{
				connect();
			}
		}
		catch (SQLException e1)
		{
			e1.printStackTrace();
		}

		try
		{
			st = conn.createStatement();
			// run the query
			rs = st.executeQuery(Messages.getString("HSQLDBUtil.140"));

			int playerid = 0;
			String name = Messages.getString("HSQLDBUtil.4"), aliases = Messages.getString("HSQLDBUtil.4");
			int updatecount = 0;
			for (; rs.next();)
			{
				// process each row
				name = rs.getString(1);
				playerid = rs.getInt(2);
				aliases = rs.getString(3);
				if (aliases.indexOf(Messages.getString("HSQLDBUtil.71")) >= 0)
				{
					aliases = StringUtils.oldReplaceAll(aliases, Messages.getString("HSQLDBUtil.71"), Messages.getString("HSQLDBUtil.73"));
					if (PropertyManager.isDebug())
					{
						System.out.println(Messages.getString("HSQLDBUtil.146") + aliases);
					}
				}
				if (aliases.equals(Constants.ALIAS_SEPERATOR + name))
				{
					aliases = name;
				}
				else
				{// search first
					if (aliases.indexOf(name) > -1)
					{
						// already contains. just remove the initial seperator
						aliases = aliases.substring(Constants.ALIAS_SEPERATOR.length());
					}
					else
					{
						aliases = name + aliases;
					}
				}

				// write back
				update(Messages.getString("HSQLDBUtil.147") + aliases + Messages.getString("HSQLDBUtil.148") + playerid);
				updatecount++;
			}
			System.out.println(Messages.getString("HSQLDBUtil.149") + updatecount + Messages.getString("HSQLDBUtil.150"));
			st.close();
		}
		catch (SQLException e)
		{
			e.printStackTrace();
			System.out.println(Messages.getString("HSQLDBUtil.151") + e.getMessage());
		}
	}

	public static long ipToLong(String addr)
	{
		String[] addrArray = addr.split("\\.");

		long num = 0;
		for (int i = 0; i < addrArray.length; i++)
		{
			int power = 3 - i;
			num += ((Integer.parseInt(addrArray[i]) % 256 * Math.pow(256, power)));
		}
		return num;
	}

	public void disconnect() {
		try {
			if(conn!=null && conn.isClosed()==false)conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
			System.out.println(Messages.getString("HSQLDBUtil.151") + e.getMessage());
		}
		
	}
}
